package com.tuanchauict.android.englishtip.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

	public static final String dbName = "EnglishTipDB";
	public static final String tableDict = "Dictionary";
	public static final String colID = "cid";
	public static final String colWord = "word";
	public static final String colPro = "pronunciation";
	public static final String colGrade = "grade";
	public static final String colMean = "mean";

	public static final String tableConf = "Configuration";
	public static final String colKey = "key";
	public static final String colVal = "value";

	public DatabaseHelper(Context context) {
		super(context, dbName, null, 33);

	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub

		db.execSQL("CREATE TABLE " + tableDict + "(" + colID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + colWord + " TEXT,"
				+ colPro + " TEXT," + colGrade + " INTEGER," + colMean
				+ " TEXT);");

		db.execSQL("CREATE TABLE " + tableConf + " (" + colKey + " TEXT, "
				+ colVal + " INTEGER);");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + tableDict);
		db.execSQL("DROP TABLE IF EXISTS " + tableConf);

		onCreate(db);
	}

	public int insertWord(String word, String pron, int grade, String mean) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(colWord, word);
		cv.put(colPro, pron);
		cv.put(colGrade, grade);
		cv.put(colMean, mean);
		db.insert(tableDict, colID, cv);
		db.close();
		Cursor c = db.query(tableDict, new String[] { colID + " as id" },
				colWord + " = " + toQuote(word) + " and " + colGrade + " = "
						+ grade, null, null, null, null);
		c.moveToLast();
		return c.getInt(0);
	}

	public int insertWord(Word w) {
		int id = insertWord(w.word, w.pron, w.grade, w.mean);
		w.cid = id;
		return id;
	}

	public void insertConfig(String key, int val) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(colKey, key);
		cv.put(colVal, val);
		db.insert(tableConf, null, cv);
		db.close();
	}

	public int updateWord(int cid, String word, String pron, int grade,
			String mean) {
		SQLiteDatabase db = this.getReadableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(colWord, word);
		cv.put(colPro, pron);
		cv.put(colGrade, grade);
		cv.put(colMean, mean);

		return db.update(tableDict, cv, colID + " = " + cid, null);
	}

	public int updateWord(Word word) {
		return updateWord(word.cid, word.word, word.pron, word.grade, word.mean);
	}

	public int updateConfig(String key, int val) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(colVal, val);
		return db.update(tableConf, cv, colKey + " = " + key, null);
	}

	public int deteteWord(int id) {
		SQLiteDatabase db = this.getWritableDatabase();

		return db.delete(tableDict, colID + " = " + id, null);
	}

	public int deleteConf(String key) {
		SQLiteDatabase db = this.getWritableDatabase();

		return db.delete(tableDict, colID + " = " + toQuote(key), null);
	}

	private String toQuote(String str) {
		return "'" + str + "'";
	}

	public Word[] getWords() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor c = db.query(tableDict, new String[] { colID + " as id ",
				colWord + " as word ", colPro + " as pron ",
				colGrade + " as grade ", colMean + " as mean" }, null, null,
				null, null, colWord);
		int size = c.getCount();
		Word[] result = new Word[size];

		for (int i = 0; i < size; i++) {
			result[i] = new Word(c.getInt(0), c.getString(1), c.getString(2),
					c.getInt(3), c.getString(4));
			c.moveToNext();

		}

		return result;
	}

}
